﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[State]') AND type in (N'U'))
DROP TABLE [dbo].[State]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[State] (
	[Id] [uniqueidentifier] NOT NULL,
	[Data] [varbinary](max) NOT NULL,
	[DataFormat] [int] NOT NULL,
	[Pid] [varchar](30) NOT NULL,
	[Flags] [int] NOT NULL,
	[ArrivedOn] [datetime] NOT NULL,
	CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

GO

CREATE NONCLUSTERED INDEX [IX_Pid_ArrivedOn] ON [dbo].[State] 
(
	[Pid] ASC,
	[ArrivedOn] ASC
)

GO

SET ANSI_PADDING OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Delete]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Delete]
	@Id uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM [State]
	WHERE [Id] = @Id
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllStates]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAllStates]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetAllStates]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [Id], [Data], [DataFormat], [Pid], [Flags], [ArrivedOn]
	FROM [State]
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetLatestState]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetLatestState]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetLatestState]
	@Pid varchar(30)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT TOP 1 [Id], [Data], [DataFormat], [Pid], [Flags], [ArrivedOn]
	FROM [State]
	WHERE [Pid] = @Pid
	ORDER BY [ArrivedOn] DESC
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Save]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Save]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Save]
	@Id uniqueidentifier,
	@Data varbinary(max),
	@DataFormat int,
	@Pid varchar(30),
	@Flags int,
	@ArrivedOn datetime
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [State] ([Id], [Data], [DataFormat], [Pid], [Flags], [ArrivedOn])
	VALUES (@Id, @Data, @DataFormat, @Pid, @Flags, @ArrivedOn)
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateFlags]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateFlags]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateFlags]
	@Id uniqueidentifier,
	@Flags int
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [State]
	SET [Flags] = @Flags
	WHERE [Id] = @Id
END

GO

